CREATE OR REPLACE PACKAGE LotSizeSampling IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================

--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION SetInsertionDetails (ContainerName IN Container.ContainerName%TYPE
                              , ContainerId IN Container.ContainerId%TYPE
                              , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                              , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                              , MainQty IN Container.Qty%TYPE
                              , SpecName IN SpecBase.SpecName%TYPE
                              , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                              , NextInsertionNumber IN Container.InsertionNumber%TYPE
                              , ProcessTypeNames IN VARCHAR2
                              , ProcessStatuses IN VARCHAR2)
RETURN VARCHAR2
;
FUNCTION VerifyCompleteInsertion (ContainerName IN Container.ContainerName%TYPE
                                  , ContainerId IN Container.ContainerId%TYPE
                                  , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                                  , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                                  , MainQty IN Container.Qty%TYPE
                                  , SpecName IN SpecBase.SpecName%TYPE
                                  , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                                  , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                                  , ProcessTypeName IN A_ProcessType.ProcessTypeName%TYPE)
RETURN VARCHAR2
;
FUNCTION VerifyMoveOut (ContainerName IN Container.ContainerName%TYPE
                        , ContainerId IN Container.ContainerId%TYPE
                        , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                        , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                        , MainQty IN Container.Qty%TYPE
                        , SpecName IN SpecBase.SpecName%TYPE
                        , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                        , CurrentInsertionNumber IN Container.InsertionNumber%TYPE)
RETURN VARCHAR2
;
FUNCTION VerifyWIPData (ContainerName IN Container.ContainerName%TYPE
                        , ContainerId IN Container.ContainerId%TYPE
                        , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                        , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                        , SpecName IN SpecBase.SpecName%TYPE
                        , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                        , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                        , ProcessTypeNames IN VARCHAR2
                        , WIPDataNames IN VARCHAR2
                        , WIPDataValues IN VARCHAR2)
RETURN VARCHAR2
;
FUNCTION ExecuteDisposition (ContainerName IN Container.ContainerName%TYPE
                             , ContainerId IN Container.ContainerId%TYPE
                             , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                             , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                             , LotDispositionId IN Container.LotDispositionId%TYPE)
RETURN VARCHAR2
;
/*PROCEDURE CheckYield (InputData IN YieldHandler.TYPE_YieldHandler_InputData
                      , ServiceData IN OUT YieldHandler.TYPE_YieldHandler_ServiceData)
;*/
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY LotSizeSampling AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- SetInsertionDetails
--=====================
FUNCTION SetInsertionDetails (ContainerName IN Container.ContainerName%TYPE
                              , ContainerId IN Container.ContainerId%TYPE
                              , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                              , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                              , MainQty IN Container.Qty%TYPE
                              , SpecName IN SpecBase.SpecName%TYPE
                              , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                              , NextInsertionNumber IN Container.InsertionNumber%TYPE
                              , ProcessTypeNames IN VARCHAR2
                              , ProcessStatuses IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameters variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    vContainerId Container.ContainerId%TYPE := ContainerId;
    vCurrentWIPLotId Container.CurrentWIPLotId%TYPE := CurrentWIPLotId;
    vStepLogicCriteriaId Container.StepLogicCriteriaId%TYPE := StepLogicCriteriaId;
    nMainQty Container.Qty%TYPE := MainQty;
    vSpecName SpecBase.SpecName%TYPE := SpecName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := StepLogicName;
    nNextInsertionNumber Container.InsertionNumber%TYPE := NextInsertionNumber;
    cProcessTypeNames TRGUtil.COLLECTION := TRGUtil.SetCollection(ProcessTypeNames, ',');
    cProcessStatuses TRGUtil.COLLECTION := TRGUtil.SetCollection(ProcessStatuses, ',');
    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLot IS
    SELECT
        B.SpecId
        , C.ResourceGroupId
    FROM
        Container A
        , CurrentStatus B
        , Spec C
    WHERE
        A.CurrentStatusId = B.CurrentStatusId
        AND B.SpecId = C.SpecId
        AND A.ContainerId = vContainerId
    ;
    CURSOR curCriteria (IN_SpecId A_LotSizeSamplingSetup.SpecId%TYPE) IS
    SELECT
        LotSizeSamplingSetupId CriteriaId
    FROM
        A_LotSizeSamplingSetup
    WHERE
        SpecId = IN_SpecId
    ;
    CURSOR curSetupDetails (IN_Criteria A_LotSizeSamplingSetupDetails.LotSizeSamplingSetupId%TYPE) IS
    SELECT
        *
    FROM
        A_LotSizeSamplingSetupDetails
    WHERE
        LotSizeSamplingSetupId = IN_Criteria
        AND LotSizeThreshold >= nMainQty
    ORDER BY
        LotSizeThreshold ASC
    ;
    --=====================
    -- Local variables
    --=====================
    rLot curLot%ROWTYPE;
    rCriteria curCriteria%ROWTYPE;
    rSetupDetails curSetupDetails%ROWTYPE;
    nQtyToProcess NUMBER := 0;
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Fetch lot info
    --=====================
    OPEN curLot;
    FETCH curLot INTO rLot;
    IF curLot%NOTFOUND THEN
        CLOSE curLot;
        RAISE_APPLICATION_ERROR (-20005, 'LotSizeSampling_E1001 ' || vContainerName);
    ELSE
        CLOSE curLot;
    END IF;
    --=====================
    -- Check the Insertion Number
    --=====================
    IF nNextInsertionNumber = 1 THEN
        --=====================
        -- Find a setup criteria
        --=====================
        OPEN curCriteria (rLot.SpecId);
        FETCH curCriteria INTO rCriteria;
        IF curCriteria%NOTFOUND THEN
            CLOSE curCriteria;
        ELSE
            CLOSE curCriteria;
            --=====================
            -- Get setup details
            --=====================
            OPEN curSetupDetails (rCriteria.CriteriaId);
            FETCH curSetupDetails INTO rSetupDetails;
            IF curSetupDetails%NOTFOUND THEN
                CLOSE curSetupDetails;
            ELSE
                CLOSE curSetupDetails;
                --=====================
                -- Save the criteria id for the lot
                --=====================
                StepLogicHandler.SaveCriteria (vContainerId, rCriteria.CriteriaId);
                --=====================
                -- Set the Qty To Process
                --=====================
                IF NVL(rSetupDetails.QtyToProcessPercentage, 0) > 0 THEN
                    nQtyToProcess := ROUND(nMainQty * rSetupDetails.QtyToProcessPercentage / 100);
                ELSE
                    nQtyToProcess := rSetupDetails.QtyToProcess;
                END IF;
            END IF;
        END IF;
        --=====================
        -- Set the insertion details
        --=====================
        IF (nQtyToProcess <= 0) OR (nQtyToProcess > nMainQty) THEN
            nQtyToProcess := nMainQty;
        END IF;
        --=====================
        -- Insert the insertion details
        --=====================
        FOR nCount IN 0..cProcessTypeNames.COUNT-1 LOOP
            StepLogicHandler.InsertInsertionDetails (vContainerId
                                                     , 1
                                                     , nQtyToProcess
                                                     , cProcessTypeNames(nCount)
                                                     , cProcessStatuses(nCount)
                                                     , '-'
                                                     , '-'
                                                     , '-'
                                                     , '-');
        END LOOP;
    ELSE
        --=====================
        -- Set the next insertion details
        --=====================
        StepLogicHandler.SetNextInsertionDetails (vContainerId);
    END IF;
    --=====================
    -- Commit the data
    --=====================
    COMMIT;
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('LotSizeSampling.SetInsertionDetails->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('LotSizeSampling.SetInsertionDetails->' || SQLERRM);
END SetInsertionDetails;

--=====================
-- VerifyCompleteInsertion
--=====================
FUNCTION VerifyCompleteInsertion (ContainerName IN Container.ContainerName%TYPE
                                  , ContainerId IN Container.ContainerId%TYPE
                                  , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                                  , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                                  , MainQty IN Container.Qty%TYPE
                                  , SpecName IN SpecBase.SpecName%TYPE
                                  , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                                  , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                                  , ProcessTypeName IN A_ProcessType.ProcessTypeName%TYPE)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameters variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    vContainerId Container.ContainerId%TYPE := ContainerId;
    vCurrentWIPLotId Container.CurrentWIPLotId%TYPE := CurrentWIPLotId;
    vStepLogicCriteriaId Container.StepLogicCriteriaId%TYPE := StepLogicCriteriaId;
    nMainQty Container.Qty%TYPE := MainQty;
    vSpecName SpecBase.SpecName%TYPE := SpecName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := StepLogicName;
    nCurrentInsertionNumber Container.InsertionNumber%TYPE := CurrentInsertionNumber;
    vProcessTypeName A_ProcessType.ProcessTypeName%TYPE := ProcessTypeName;
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    --
    --=====================

    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotSizeSampling.VerifyCompleteInsertion->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotSizeSampling.VerifyCompleteInsertion->' || SQLERRM);
END VerifyCompleteInsertion;

--=====================
-- VerifyMoveOut
--=====================
FUNCTION VerifyMoveOut (ContainerName IN Container.ContainerName%TYPE
                        , ContainerId IN Container.ContainerId%TYPE
                        , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                        , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                        , MainQty IN Container.Qty%TYPE
                        , SpecName IN SpecBase.SpecName%TYPE
                        , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                        , CurrentInsertionNumber IN Container.InsertionNumber%TYPE)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameters variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    vContainerId Container.ContainerId%TYPE := ContainerId;
    vCurrentWIPLotId Container.CurrentWIPLotId%TYPE := CurrentWIPLotId;
    vStepLogicCriteriaId Container.StepLogicCriteriaId%TYPE := StepLogicCriteriaId;
    nMainQty Container.Qty%TYPE := MainQty;
    vSpecName SpecBase.SpecName%TYPE := SpecName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := StepLogicName;
    nCurrentInsertionNumber Container.InsertionNumber%TYPE := CurrentInsertionNumber;
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    --
    --=====================

    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotSizeSampling.VerifyMoveOut->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotSizeSampling.VerifyMoveOut->' || SQLERRM);
END VerifyMoveOut;

--=====================
-- VerifyWIPData
--=====================
FUNCTION VerifyWIPData (ContainerName IN Container.ContainerName%TYPE
                        , ContainerId IN Container.ContainerId%TYPE
                        , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                        , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                        , SpecName IN SpecBase.SpecName%TYPE
                        , StepLogicName IN A_StepLogic.StepLogicName%TYPE
                        , CurrentInsertionNumber IN Container.InsertionNumber%TYPE
                        , ProcessTypeNames IN VARCHAR2
                        , WIPDataNames IN VARCHAR2
                        , WIPDataValues IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameters variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    vContainerId Container.ContainerId%TYPE := ContainerId;
    vCurrentWIPLotId Container.CurrentWIPLotId%TYPE := CurrentWIPLotId;
    vStepLogicCriteriaId Container.StepLogicCriteriaId%TYPE := StepLogicCriteriaId;
    vSpecName SpecBase.SpecName%TYPE := SpecName;
    vStepLogicName A_StepLogic.StepLogicName%TYPE := StepLogicName;
    nCurrentInsertionNumber Container.InsertionNumber%TYPE := CurrentInsertionNumber;
    cProcessTypeNames TRGUtil.COLLECTION := TRGUtil.SetCollection(ProcessTypeNames, ',');
    cWIPDataNames TRGUtil.COLLECTION := TRGUtil.SetCollection(WIPDataNames, ',');
    cWIPDataValues TRGUtil.COLLECTION := TRGUtil.SetCollection(WIPDataValues, ',');
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    --
    --=====================

    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotSizeSampling.VerifyWIPData->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotSizeSampling.VerifyWIPData->' || SQLERRM);
END VerifyWIPData;

--=====================
-- ExecuteDisposition
--=====================
FUNCTION ExecuteDisposition (ContainerName IN Container.ContainerName%TYPE
                             , ContainerId IN Container.ContainerId%TYPE
                             , CurrentWIPLotId IN Container.CurrentWIPLotId%TYPE
                             , StepLogicCriteriaId IN Container.StepLogicCriteriaId%TYPE
                             , LotDispositionId IN Container.LotDispositionId%TYPE)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameters variables
    --=====================
    vContainerName Container.ContainerName%TYPE := ContainerName;
    vContainerId Container.ContainerId%TYPE := ContainerId;
    vCurrentWIPLotId Container.CurrentWIPLotId%TYPE := CurrentWIPLotId;
    vStepLogicCriteriaId Container.StepLogicCriteriaId%TYPE := StepLogicCriteriaId;
    vLotDispositionId Container.LotDispositionId%TYPE := LotDispositionId;
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    --
    --=====================

    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotSizeSampling.ExecuteDisposition->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotSizeSampling.ExecuteDisposition->' || SQLERRM);
END ExecuteDisposition;

--=====================
-- CheckYield
--=====================
/*PROCEDURE CheckYield (InputData IN YieldHandler.TYPE_YieldHandler_InputData
                      , ServiceData IN OUT YieldHandler.TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check for a criteria id
    --=====================
    IF ServiceData.StepLogicCriteriaId IS NOT NULL THEN
        ServiceData.FailStepLogic := 0;
    END IF;
EXCEPTION
    WHEN eExit THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotSizeSampling.CheckYield->Exited prematurely');
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotSizeSampling.CheckYield->' || SQLERRM);
END CheckYield;
*/
--===========================================================================
-- End of package body
--===========================================================================
END;
/
